package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.edu.bean.Book;
import com.edu.bean.User;
import com.edu.dao.BaseDao;
import com.edu.dao.BookDao;

public class BookDaoImpl implements BookDao {

	@Override
	public List<Book> selectAll() {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Book> books = new ArrayList();
		String sql = "SELECT * FROM book";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			res = ps.executeQuery();
			while(res.next()) {
				Book book = new Book();
				book.setBookId(res.getInt("bookId"));
				book.setBookName(res.getString("bookName"));
				book.setBookNum(res.getInt("bookNum"));
				book.setBookPrice(res.getDouble("bookPrice"));
				book.setBookWriter(res.getString("bookWriter"));
				book.setBookType(res.getString("bookType"));
				books.add(book);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return books;
	}

	@Override
	public int deleteBookById(int bookId) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "DELETE FROM book WHERE bookId = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,bookId);
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		
		return result;
	}

	@Override
	public int insertBook(Book book) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "INSERT INTO book(bookName,bookPrice,bookWriter,bookNum,bookType) VALUES "
				+ "(?,?,?,?,?)";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, book.getBookName());
			ps.setDouble(2, book.getBookPrice());
			ps.setString(3, book.getBookWriter());
			ps.setInt(4, book.getBookNum());
			ps.setString(5, book.getBookType());
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public Book selectBookById(int bookId) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		Book book = null;
		String sql = "SELECT * FROM book WHERE bookId = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1,bookId);
			res = ps.executeQuery();
			if(res.next()) {
				book = new Book();
				book.setBookId(res.getInt("bookId"));
				book.setBookName(res.getString("bookName"));
				book.setBookNum(res.getInt("bookNum"));
				book.setBookPrice(res.getDouble("bookPrice"));
				book.setBookWriter(res.getString("bookWriter"));
				book.setBookType(res.getString("bookType"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return book;
	}

	@Override
	public int updateBook(Book book) {
		Connection con = null;
		PreparedStatement ps = null;
		int result = 0;
		String sql = "UPDATE book set "
				+ "bookName = ?,bookPrice = ?,bookWriter = ?,bookNum = ?,bookType= ?"
				+ " WHERE bookId = ? ";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, book.getBookName());
			ps.setDouble(2, book.getBookPrice());
			ps.setString(3, book.getBookWriter());
			ps.setInt(4, book.getBookNum());
			ps.setString(5, book.getBookType());
			ps.setInt(6, book.getBookId());
			result = ps.executeUpdate();
		} catch (SQLException e) {			
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, null);
		}
		return result;
	}

	@Override
	public List<Book> selectBookByBookName(String bookName) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet res = null;
		List<Book> books = new ArrayList();
		String sql = "SELECT * FROM book WHERE bookName like concat('%',?,'%')";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setString(1, bookName);
			res = ps.executeQuery();
			while(res.next()) {
				Book book = new Book();
				book.setBookId(res.getInt("bookId"));
				book.setBookName(res.getString("bookName"));
				book.setBookNum(res.getInt("bookNum"));
				book.setBookPrice(res.getDouble("bookPrice"));
				book.setBookWriter(res.getString("bookWriter"));
				book.setBookType(res.getString("bookType"));
				books.add(book);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, res);
		}
		
		return books;
	}

}
